package grimbo.goodwinfooty.xls;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

public class XLS2CSV {
    private InputStream in;
    private PrintStream out;
    private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");

    public XLS2CSV(InputStream in, PrintStream out) {
        if (null == in) {
            throw new IllegalArgumentException("in cannot be null.");
        }
        if (null == out) {
            throw new IllegalArgumentException("out cannot be null.");
        }
        this.in = in;
        this.out = out;
    }

    public void process() throws IOException {
        process(in);
    }

    private void process(InputStream in) throws IOException {
        HSSFWorkbook w = new HSSFWorkbook(in);
        int numSheets = w.getNumberOfSheets();
        for (int i = 0; i < numSheets; i++) {
            HSSFSheet sheet = w.getSheetAt(i);
            int lastRow = 0;
            for (Iterator rows = sheet.rowIterator(); rows.hasNext();) {
                Row row = (Row) rows.next();
                int lastCol = 0;
                for (Iterator cells = row.cellIterator(); cells.hasNext();) {
                    Cell cell = (Cell) cells.next();
                    String cellValue = "";
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_FORMULA:
                        FormulaEvaluator fe = new HSSFFormulaEvaluator(w);
                        CellValue v = fe.evaluate(cell);
                        switch (v.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(v.getBooleanValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cellValue = String.valueOf(v.getNumberValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cellValue = String.valueOf(v.getStringValue());
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            break;
                        // CELL_TYPE_FORMULA will never happen
                        case Cell.CELL_TYPE_FORMULA:
                            break;
                        }
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            cellValue = dateFormat.format(date);
                        } else {
                            cellValue = String.valueOf(cell);
                        }
                        break;
                    default:
                        cellValue = String.valueOf(cell);
                    }
                    int cellIndex = cell.getColumnIndex();
                    while (lastCol < cellIndex) {
                        out.print(",");
                        lastCol++;
                    }
                    out.print(cellValue);
                }
                int rowIndex = row.getRowNum();
                while (lastRow <= row.getRowNum()) {
                    out.println();
                    lastRow++;
                }
            }
        }
    }

    public void setDateFormat(DateFormat dateFormat) {
        if (null == dateFormat) {
            throw new IllegalArgumentException("dateFormat cannot be null.");
        }
        this.dateFormat = dateFormat;
    }

    public DateFormat getDateFormat() {
        return dateFormat;
    }

    public static void process(File file, PrintStream out) throws IOException {
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            new XLS2CSV(new BufferedInputStream(fis), out).process();
        } finally {
            if (null != fis) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
